# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# ORM base
base = declarative_base()

class User(base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True,autoincrement=True)
    name = Column(String(20))
    age = Column(Integer)
    def get_json(self):
        return {
            'id': self.id,
            'name': self.name,
            'age': self.age
        }

# 定义数据库连接
engine = create_engine('sqlite:////home/jiangzx/project/data-mining-2024-fall/code/test.db')
DBsession = sessionmaker(bind=engine)

# 创建表
base.metadata.create_all(engine)



session = DBsession()

#增
new_user = User(name='test2',age=10)
session.add(new_user)
#删
session.query(User).filter(User.id == 2).delete()
#改
session.query(User).filter(User.id == 3).update({User.name: "update_test"})

session.commit()
#查
people = session.query(User).all()
for item in people:
    print(item.get_json())
session.close()